#    tribalgrowth is a growth monitor for the online game Tribal Wars.
#    Copyright 2009 Nathan Michaels                                            
#                                                                              
#    This file is part of tribalgrowth.                                        
#                                                                              
#    tribalgrowth is free software: you can redistribute it and/or modify      
#    it under the terms of the GNU General Public License as published by      
#    the Free Software Foundation, either version 2 of the License, or         
#    (at your option) any later version.                                       
#                                                                              
#    tribalgrowth is distributed in the hope that it will be useful,           
#    but WITHOUT ANY WARRANTY; without even the implied warranty of            
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the             
#    GNU General Public License for more details.                              
#                                                                              
#    You should have received a copy of the GNU General Public License         
#    along with tribalgrowth.  If not, see <http://www.gnu.org/licenses/>. 

from pysqlite2 import dbapi2 as db
import pdb


# Create a bunch of delta tables to match the village tables. Each
# delta table should have the following columns:

# start_time, end_time, name_changed, player_changed, dpoints, drank

# The _changed fields are boolean. Everything else is self explanatory.

class DeltaDB (object):
    def __init__ (self, fileName='tribal.db'):
        self.connection = db.connect (fileName)
        self.cursor = self.connection.cursor ()

        self.cursor.execute ('SELECT id FROM villages')
        villageIds = self.cursor.fetchall()

        self.villages = []
        self.deltas = []
        for villId in villageIds:
            ID = str (villId[0])
            self.villages.append ('village' + ID)
            self.deltas.append ('delta' + ID)

    def CheckDeltaTable (self, dTable):
        """Make sure there's a delta table for the village in vTable."""
        # start_time, end_time, name_changed, player_changed, dPoints, dRank
        self.cursor.execute (
         '''CREATE TABLE IF NOT EXISTS %s 
            (
                start_time INTEGER PRIMARY KEY,
                end_time INTEGER,
                name_changed INTEGER,
                player_changed INTEGER,
                dPoints INTEGER,
                dRank INTEGER
            )''' % dTable)

    def IsAlreadyThere (self, dTable, dates):
        """Return True if there's already an entry in dTable for the two
        dates specified in dates."""
        dates = dates[:]
        dates.sort()
        select = 'SELECT * FROM %s WHERE start_time="%s" AND end_time="%s"' % (
            dTable, 
            dates[0],
            dates[1])
        self.cursor.execute (select)

        selected = self.cursor.fetchall ()
        if selected:
            return True
        return False

    def AddDelta (self, dTable, vTable, dates):
        """Add the difference between dates[0] and dates[1] to dTable. Ignore
        ones that already exist."""
        dates = dates[:]
        if self.IsAlreadyThere (dTable, dates):
            return

        dates.sort()
        select = """SELECT date, name, player, points, rank FROM %s WHERE 
                    date=%s OR date=%s ORDER BY date""" % (vTable, dates[0],
                                                           dates[1])

        self.cursor.execute (select)
        rows = self.cursor.fetchall ()

        oldDate, oldName, oldPlayer, oldPoints, oldRank = rows[0]
        newDate, newName, newPlayer, newPoints, newRank = rows[1]
        assert (oldDate < newDate)

        dName = oldName != newName
        dPlayer = oldPlayer != newPlayer
        dPoints = newPoints - oldPoints
        dRank = newRank - oldRank
        
        insert = "INSERT INTO %s VALUES (?, ?, ?, ?, ?, ?)" % (dTable,)
        self.cursor.execute (insert, (oldDate, newDate, dName, dPlayer, dPoints,
                                      dRank))

    def Update (self, dTable, vTable):
        """Do a full update of delta table dTable by looking at village table
        vTable. Check every pair of consecutive dates in vTable and make sure
        they're in dTable. Verify that only consecutive date pairs appear in
        dTable."""
        # Translation: drop the table and rebuild it from scratch.
        self.cursor.execute ('DROP TABLE IF EXISTS %s' % dTable)
        self.CheckDeltaTable (dTable)
        dateSql = 'SELECT date FROM %s' % (vTable)
        self.cursor.execute (dateSql)
        dates = [i[0] for i in self.cursor.fetchall ()]
        dates.sort ()
        if len (dates) < 2:
            return
        for first in range(len(dates)-1):
            self.AddDelta (dTable, vTable, [dates[first], dates[first+1]])

    def UpdateAll (self):
        """Do a full update of all the tables. This ought to take a while."""
        for village, delta in zip (self.villages, self.deltas):
            self.Update (delta, village)
        self.connection.commit ()

    def QuickUpdate (self):
        """Update all delta tables but only for the last 2 dates in each
        village table."""
        for village, delta in zip (self.villages, self.deltas):
            select = 'SELECT date from %s ORDER BY date' % (village,)
            self.cursor.execute (select)
            dates = self.cursor.fetchall()
            if len (dates) < 2:
                continue
            self.CheckDeltaTable (delta)
            self.AddDelta (delta, village, [dates[-1][0], dates[-2][0]])
        self.connection.commit ()

if __name__=='__main__':
    base = DeltaDB ()
    base.UpdateAll ()
